﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

// EEJ Added usings
using System.Data;
using System.Data.SqlServerCe;
using ErikEJ.SqlCe;
using System.IO;
using System.Reflection;

namespace BuildSDFDatabase
{
	public class TablesCreater
	{
		public static string TablesCreate(int index)
		{
			string execute = "";

			switch (index)
			{
				case 0: execute = CorporaTable; break;
				case 1: execute = FieldTable; break;
				case 2: execute = ClusterInfoTable; break;
				case 3: execute = CorpusTimeClusterTable; break;
				case 4: execute = CorpusTimeClusterKeyword; break;
				case 5: execute = Venue; break;
				case 6: execute = NSFOrganization; break;
				case 7: execute = NSFProgram; break;
				case 8: execute = Document; break;
				case 9: execute = Author; break;
				case 10: execute = AuthorShip; break;
				case 11: execute = ClusterDocument; break;
				case 12: execute = ClusterDocumentID_Index; break;
				case 13: execute = RoseTree; break;
				case 14: execute = GlobalLeadLag; break;
				case 15: execute = LocalLeadLag; break;
				case 16: execute = DocumentMatrix; break;
				case 17: execute = DocumentMatrix_Index; break;
				case 18: execute = DocVenueID_Index; break;
				case 19: execute = DocOrganizationID_Index; break;
				case 20: execute = DocProgramID_Index; break;
				case 21: execute = DataTimeTable; break;
				case 22: execute = DisplayTree; break;
				case 23: execute = CorpusTreeNodeTable; break;
				case 24: execute = CorpusTreeNodeKeyword; break;
				case 25: execute = CorpusTreeNodeKeywordTFIDF; break;
			}

			return execute;
		}

		public static string DataTimeTable = @"Create Table DataTime(
													StartTime	datetime not null,
													EndTime		datetime not null);";

		public static string CorporaTable = @"Create Table Corpora(
													CorpusID	int Primary Key,
													CorpusName	nvarchar(31) not null)";

		public static string FieldTable = @"Create Table Field(
													FieldID			int Primary Key,
													FieldName		nvarchar(31) not null);";

		public static string ClusterInfoTable = @"Create Table ClusterInfo(
													FieldID		int,
													ClusterID	int not null,
													Timestamp	datetime not null,
													Size		int not null,
													Constraint	pk_ClusterInfo Primary Key(FieldID, Timestamp, ClusterID));";

		public static string CorpusTimeClusterTable = @"Create Table CorpusTimeCluster(
													FieldID		int,
													ClusterID	int not null,
													Timestamp	datetime not null,
													CorpusID	int,
													Size		int not null,
													Constraint	pk_CorpusTimeCluster Primary Key(FieldID, Timestamp, ClusterID, CorpusID));";

		public static string CorpusTimeClusterKeyword = @"Create Table CorpusTimeClusterKeyword(
													FieldID		int,
													ClusterID	int not null,
													Timestamp	datetime not null,
													CorpusID	int,
													Keyword		nvarchar(31) not null,
													Frequency	int,
													Constraint	pk_CorpusTimeClusterKeyword Primary Key(FieldID, Timestamp, ClusterID, CorpusID, Keyword));";

		public static string Venue = @"Create Table Venue(
													VenueID					int Primary Key,
													VenueName				nvarchar(63) not null);";

		public static string NSFOrganization = @"Create Table NSFOrganization(
													NSFOrganizationID		int Primary Key,
													NSFOrganizationName		nvarchar(63) not null);";

		public static string NSFProgram = @"Create Table NSFProgram(
													NSFProgramID			int Primary Key,
													NSFProgramName			nvarchar(63) not null);";

		public static string Document = @"Create Table Document(
													DocumentID			int Primary Key,
													Timestamp			datetime not null,
													Title				ntext,
													Abstract			ntext,
													CorpusID			int,
													
													Keywords			ntext,
													VenueID				int,
													
													NSFOrganizationID	int,
													AwardedAmountToDate int,
													NSFProgramID		int);";
		public static string DocTimeStamp_Index = @"Create Index Timestamp_Index on Document(Timestamp);";
		public static string DocVenueID_Index = @"Create Index VenueID_Index on Document(VenueID);";
		public static string DocOrganizationID_Index = @"Create Index OrganizationID_Index on Document(NSFOrganizationID);";
		public static string DocProgramID_Index = @"Create Index ProgramID_Index on Document(NSFProgramID);";

		public static string Author = @"Create Table Author(
													AuthorID		int Primary Key,
													FirstName		nvarchar(31) not null,
													LastName		nvarchar(31) not null);";

		public static string AuthorShip = @"Create Table AuthorShip(
													DocumentID		int,
													AuthorID		int,
													Rank			int,
													Constraint pk_AuthorShip Primary Key(DocumentID, AuthorID));";

		public static string ClusterDocument = @"Create Table ClusterDocument(
													DocumentID		int,
													FieldID			int,
													ClusterID		int not null,
													CorpusID		int,
													constraint	pk_ClusterDoc primary key(DocumentID));";
		public static string ClusterDocumentID_Index = @"Create Index ClusterDocID_Index on ClusterDocument(FieldID, ClusterID, CorpusID);";

		public static string RoseTree = @"Create Table RoseTree(
																FieldID		int,
																Child		nvarchar(31) not null,
																Parent		nvarchar(31) not null,
																Constraint	pk_RoseTree Primary Key(FieldID, Child));";

		public static string GlobalLeadLag = @"Create Table GlobalLeadLag(
													FieldID			int,
													ObjectID		nvarchar(31) not null,
													CorpusID		int,
													LeadLagValue	float,
													Constraint	pk_GlobalLeadLag Primary Key(FieldID, ObjectID, CorpusID));";

		public static string LocalLeadLag = @"Create Table LocalLeadLag(
													FieldID			int,
													ClusterID		int not null,
													Timestamp		datetime not null,
													CorpusID1		int,
													CorpusID2		int,
													Value1			float,
													Value2			float,
													Constraint	pk_LocalLeadLag Primary Key(FieldID, ClusterID, Timestamp, CorpusID1, CorpusID2));";

		public static string DocumentMatrix = @"Create Table DocumentMatrix(
													FieldID			int,
													ClusterID		int not null,
													CorpusID1		int,
													CorpusID2		int,
													DocumentID1		int not null,
													DocumentID2		int not null,
													Similarity		float,
													Constraint	pk_DocumentMatrix Primary Key(DocumentID1, DocumentID2));";
		public static string DocumentMatrix_Index = @"Create Index DocumentMatrixFieldCluster_Index on DocumentMatrix(FieldID, ClusterID, CorpusID1, CorpusID2);";

		public static string DisplayTree = @"Create Table DisplayTree(
																CorpusID		int,
																ChildNodeId		int not null,
																ChildIsBundle	bit,
																ParentNodeId	int not null,
																Constraint	pk_DisplayTree Primary Key(CorpusID, ChildNodeId, ChildIsBundle));";

		public static string CorpusTreeNodeTable = @"Create Table CorpusTreeNode(
													NodeID			int,
													IsBundle		bit not null,
													CorpusID		int,
													Size			int not null,
													StartTime		datetime,
													EndTime			datetime,
													LeadingFactor	float,
													Constraint	pk_CorpusTreeNode Primary Key(NodeID, IsBundle, CorpusID));";

		public static string CorpusTreeNodeKeyword = @"Create Table CorpusTreeNodeKeyword(
													NodeID		int,
													IsBundle	bit not null,
													CorpusID	int,
													Keyword		nvarchar(31) not null,
													Frequency	int,
													Constraint	pk_CorpusTreeNodeKeyword Primary Key(NodeID, IsBundle, CorpusID, Keyword));";

		public static string CorpusTreeNodeKeywordTFIDF = @"Create Table CorpusTreeNodeKeywordTFIDF(
													NodeID		int,
													IsBundle	bit not null,
													CorpusID	int,
													Keyword		nvarchar(31) not null,
													Value		float,
													Constraint	pk_CorpusTreeNodeKeywordTFIDF Primary Key(NodeID, IsBundle, CorpusID, Keyword));";
	}
}
